package db;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import util.QueryBuilder;
import model.Apartment;
import model.Installation;
import model.InstallationType;
import model.Rent;
import model.Report;
import model.ReportEvaluation;
import model.ReportType;

public class DBReportEvaluation implements IFDBReportEvaluation
{
	private Connection con;
	QueryBuilder qb;
	public DBReportEvaluation()
	{
		con = DBConnection.getInstance().getDBcon();
		qb = new QueryBuilder();
//		/* end custom */
	}
	private ReportEvaluation buildObject(ResultSet results)
	{
		ReportEvaluation item = new ReportEvaluation();
		Installation inst = new Installation();
		Report rmo = new Report();
		Report rmi = new Report();
		try
		{
//			i.id as installationId, i.serialNumber, i.garantyExpDate, 
//			i.type as installationType,
//			RMO.id as rmoId, rmo.condition as rmoCondition, 
//			rmi.id as rmiId, rmi.condition as rmiCondition 
			
			inst.setId(results.getInt("installationId"));
			inst.setInstallDate(qb.toUtil(results.getDate("installDate")));
			inst.setSerialNumber(results.getString("serialNumber"));
			inst.setType(InstallationType.getType(results.getInt("installationType")));
			inst.setGarantyExpDate(qb.toUtil(results.getDate("garantyExpDate")));
			inst.setApartment(new Apartment(results.getInt("apartmentId")));
			
			rmo.setCondition(results.getString("rmoCondition"));
			rmo.setId(results.getInt("id"));
			rmo.setInstallation(inst);
			rmo.setType(ReportType.MOVEOUT);
			rmo.setRent(new Rent(results.getInt("id")));
			
			rmi.setCondition(results.getString("rmiCondition"));
			rmi.setId(results.getInt("id"));
			rmi.setInstallation(inst);
			rmi.setType(ReportType.MOVEIN);
			rmi.setRent(new Rent(results.getInt("id")));
			
			item.setInstallation(inst);
			item.setMiReport(rmi);
			item.setMoReport(rmo);
			/* custom */
//			item.setId(results.getInt("id"));
//			item.setNr(results.getInt("nr"));
//			item.setAddress(results.getString("address"));
//			item.setStatus(ReportEvaluationStatus.getStatus(results.getInt("status")));
//			item.setType(new ReportEvaluationType(results.getInt("appartmentTypeId")));
			/* end custom */
		} catch (Exception e)
		{
			System.out.println("error in building object");
		}
		return item;
	}
	@Override
	public ArrayList<ReportEvaluation> getByApartmentId(int apartmentId, int rentId)
	{
		//String condition = "where name='" + name + "'";
		return this.selectMultiple(apartmentId, rentId);
	}
	
	private ArrayList<ReportEvaluation> selectMultiple(int apartmentId, int rentId)
	{
		ArrayList<ReportEvaluation> list = new ArrayList<ReportEvaluation>();
		PreparedStatement ps = null;
		
		String select ="select "
		+"i.id as installationId, i.serialNumber, i.garantyExpDate, i.type as installationType, i.installDate, i.apartmentId,"
		+"RMO.id as rmoId, rmo.condition as rmoCondition, rmo.rentId as rmoRentId, "
		+"rmi.id as rmiId, rmi.condition as rmiCondition, rmi.rentId as rmiRentId "
		+"from Installation i  "
		+ "left outer join Report RMI on rmi.installationID = i.id "
		+ "left outer join Report RMO on rmo.installationID = I.id "
		+"where I.apartmentID = " + apartmentId + " "
		+"and RMI.Type = " + ReportType.MOVEIN.getIndex() + " "
		+"and RMO.Type = " + ReportType.MOVEOUT.getIndex() + " "
		+"and RMO.rentId = " + rentId + " "
		+"and RMO.rentId = " + rentId + " ";
		

		try
		{
			ps = con.prepareStatement(select);
			ResultSet rs = ps.executeQuery();
			while (rs.next())
			{
				ReportEvaluation item = new ReportEvaluation();
				item = this.buildObject(rs);
				list.add(item);
			}
		} catch (SQLException e)
		{
			e.printStackTrace();
		} finally
		{
			if (ps != null)
			{
				try
				{
					ps.close();
				} catch (SQLException e)
				{
					e.printStackTrace();
				}
			}
		}
		return list;
	}
}
